package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IFormulaManageDao;
import com.boss.salary.entity.FormulaManage;
import com.boss.sys.utils.SysUtils;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

@Repository
public class FormulaManageDaoImpl extends BaseDaoImpl implements IFormulaManageDao {

    @Override
    public List<FormulaManage> selectFormulaManagePage(PaginationDTO paginationDTO, String agencyId, Map<String, Object> queryForm) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select ")
                .append(" fme.id, fme.update_time, fme.create_time, fme.salary_id, fme.merit_pay_code, ")
                .append(" fme.formula, fme.open_type, fme.calc_order, fme.is_deleted, fme.mof_div_code ")
                .append(" from ").append(TableName.FORMULA_MANAGE).append(" fme ")
                .append(" join ").append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" on ").append(" sba.id = fme.salary_id AND sba.is_deleted = 0 ")
                .append(" AND sba.base_state = '1' AND sba.is_compute = 1 ")
                .append(" where ")
                .append(" fme.is_deleted = 0 and agency_id = ? AND fme.open_type = '1' ");
        conditions.add(agencyId);
        if (StringUtils.isEmpty(queryForm.get("mof_div_code"))){
            sb.append(" and fme.mof_div_code = ? ");
            conditions.add(queryForm.get("mof_div_code"));
        }
        if (!CollectionUtils.isEmpty(queryForm)) {
            if (!StringUtils.isEmpty(queryForm.get("salaryId"))) {
                String salaryId = String.valueOf(queryForm.get("salaryId"));
                sb.append(" and fme.salary_id = ? ");
                conditions.add(salaryId);
            }
        }
        sb.append(" order by fme.calc_order ");
        String sql = this.boxUpSql(sb.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(conditions.toArray(), paginationDTO);
        return super.getjdbcTemplate().query(sql, params, new BeanPropertyRowMapper<>(FormulaManage.class));
    }

    @Override
    public int selectFormulaManageTotal(String agencyId, Map<String, Object> queryForm) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select ")
                .append(" count(*) ")
                .append(" from ").append(TableName.FORMULA_MANAGE).append(" fme ")
                .append(" join ").append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" on ").append(" sba.id = fme.salary_id AND sba.is_deleted = 0 ")
                .append(" AND sba.base_state = '1' AND sba.is_compute = 1 ")
                .append(" where ")
                .append(" fme.is_deleted = 0 and agency_id = ? AND fme.open_type = '1' ");
        conditions.add(agencyId);
        if (StringUtils.isEmpty(queryForm.get("mof_div_code"))){
            sb.append(" and fme.mof_div_code = ? ");
            conditions.add(queryForm.get("mof_div_code"));
        }
        if (!CollectionUtils.isEmpty(queryForm)) {
            if (!StringUtils.isEmpty(queryForm.get("salaryId"))) {
                String salaryId = String.valueOf(queryForm.get("salaryId"));
                sb.append(" and fme.salary_id = ? ");
                conditions.add(salaryId);
            }
        }
        return super.getjdbcTemplate().queryForObject(sb.toString(), int.class, conditions.toArray());
    }

    @Override
    public List<FormulaManage> selectFormulaBySalaryId(String agencyId, List<String> salaryIds) {
        if (CollectionUtils.isEmpty(salaryIds)) {
            return Collections.emptyList();
        }
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ")
                .append(" fum.id, fum.salary_id, fum.formula, fum.merit_pay_code,sb.decimal_place ")
                .append(" from ")
                .append(TableName.FORMULA_MANAGE).append(" fum")
                .append(" join ").append(TableName.SALARY_BASEDATA).append("  sb ")
                .append(" on fum.salary_id = sb.id ")
                .append(" where ")
                .append(" fum.is_deleted = 0 and fum.open_type = '1' and fum.agency_id = ? ");
        paramList.add(agencyId);
//        sb.append(" and fum.salary_id in (");
//        for (int i = 0; i < salaryIds.size(); i++) {
//            if (i == salaryIds.size() - 1) {
//                sb.append(" ?) ");
//            } else {
//                sb.append(" ?, ");
//            }
//            paramList.add(salaryIds.get(i));
//        }
        sb.append(" order by fum.calc_order ");
        return super.getjdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(FormulaManage.class));
    }

    @Override
    public void updateOpenTypeBySalaryId(String agencyId, String salaryId, String openType,String meritPayCode) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.FORMULA_MANAGE)
                .append(" set ")
                .append(" update_time = ? ,open_type = ? ")
                .append(" where ")
                .append(" is_deleted = 0 and open_type = '1' and agency_id = ? and salary_id = ? and merit_pay_code=? ");
        super.getjdbcTemplate().update(sb.toString(),
                String.valueOf(DateUtils.getCurrentTimeStamp()), openType, agencyId, salaryId,meritPayCode);
    }

    @Override
    public void insertFormulaManage(FormulaManage formulaManage) {
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into ")
                .append(TableName.FORMULA_MANAGE)
                .append(" (id, create_time, update_time, salary_id, formula, open_type, ")
                .append(" calc_order, is_deleted, agency_id, mof_div_code, merit_pay_code) ")
                .append(" values(?,?,?,?,?,?,?,?,?,?,?) ");
        Object[] params = {
                SysUtils.generateGuid(), DateUtils.getCurrentTimeStamp(), DateUtils.getCurrentTimeStamp(),
                formulaManage.getSalaryId(), formulaManage.getFormula(), formulaManage.getOpenType(),
                formulaManage.getCalcOrder(), formulaManage.getIsDeleted(), formulaManage.getAgencyId(),
                formulaManage.getMofDivCode(), formulaManage.getMeritPayCode()
        };
        super.getjdbcTemplate().update(sb.toString(), params);
    }

    @Override
    public void updateFormulaManage(FormulaManage formulaManage) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.FORMULA_MANAGE)
                .append(" set ")
                .append(" update_time = ? ,formula = ? ,calc_order = ? ,merit_pay_code = ?")
                .append(" where ")
                .append(" is_deleted = 0 and open_type = '1' and id = ? ");
        Object[] params = {
                DateUtils.getCurrentTimeStamp(), formulaManage.getFormula(),
                formulaManage.getCalcOrder(), formulaManage.getMeritPayCode(),
                formulaManage.getId()
        };
        super.getjdbcTemplate().update(sb.toString(), params);
    }

    @Override
    public FormulaManage selectFormulaDetail(String id) {
        StringBuilder sb = new StringBuilder();
        sb.append(" select ")
                .append(" id,update_time,create_time,salary_id,formula,open_type,calc_order,is_deleted ,merit_pay_code")
                .append(" from ")
                .append(TableName.FORMULA_MANAGE)
                .append(" where ")
                .append(" is_deleted = 0 and open_type = '1' and id = ? ");
        return super.getjdbcTemplate().queryForObject(sb.toString(), new BeanPropertyRowMapper<>(FormulaManage.class), id);
    }

    @Override
    public void deleteFormulaById(String id) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.FORMULA_MANAGE)
                .append(" set ")
                .append(" update_time = ?, is_deleted = 1 ")
                .append(" where ")
                .append(" is_deleted = 0 and id = ? ");
        super.getjdbcTemplate().update(sb.toString(), String.valueOf(DateUtils.getCurrentTimeStamp()), id);
    }

    @Override
    public List<FormulaManage> selectFormulaBySalaryId(String province, List<String> agencyIds, List<String> computeSalaryIds) {
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sb.append(" select ").append(" salary_id, formula, calc_order, agency_id, merit_pay_code ")
                .append(" from ").append(TableName.FORMULA_MANAGE)
                .append(" where ").append(" is_deleted = 0 and open_type = '1' and mof_div_code = ? ");
        paramList.add(province);
        if(!CollectionUtils.isEmpty(agencyIds)){
            sb.append(" and ").append(SysUtils.inSql("agency_id", agencyIds));
        }
        if(!CollectionUtils.isEmpty(computeSalaryIds)){
            sb.append(" and ").append(SysUtils.inSql("salary_id", computeSalaryIds));
        }
        sb.append(" order by calc_order ");
        return super.getjdbcTemplate().query(sb.toString(), paramList.toArray(), new BeanPropertyRowMapper<>(FormulaManage.class));
    }

    @Override
    public List<FormulaManage> selectFormulaManage(String agencyId, String province) {
        StringBuilder sb = new StringBuilder();
        List<Object> conditions = new ArrayList<>();
        sb.append(" select ")
                .append(" fme.id, fme.update_time, fme.create_time, fme.salary_id, fme.merit_pay_code, ")
                .append(" fme.formula, fme.open_type, fme.calc_order, fme.is_deleted, fme.mof_div_code ")
                .append(" from ").append(TableName.FORMULA_MANAGE).append(" fme ")
                .append(" join ").append(TableName.SALARY_BASEDATA).append(" sba ")
                .append(" on ").append(" sba.id = fme.salary_id AND sba.is_deleted = 0 ")
                .append(" AND sba.base_state = '1' AND sba.is_compute = 1 ")
                .append(" where ")
                .append(" fme.is_deleted = 0 and  agency_id = ? AND fme.open_type = '1' ");
        conditions.add(agencyId);
            sb.append(" and fme.mof_div_code = ? ");
            conditions.add(province);
        //请求参数初始化
        return this.getjdbcTemplate().query(sb.toString(),  new BeanPropertyRowMapper<>(FormulaManage.class),conditions.toArray());
    }


    @Override
    public void insertFormula(List<FormulaManage> formulaManageList) {

        StringBuilder sb = new StringBuilder();
        sb.append(" insert into ")
                .append(TableName.FORMULA_MANAGE)
                .append(" (id, create_time, update_time, salary_id, formula, open_type, ")
                .append(" calc_order, is_deleted, agency_id, mof_div_code, merit_pay_code) ")
                .append(" values(?,?,?,?,?,?,?,?,?,?,?) ");
        List<Object[]> paramsList = new ArrayList<>(formulaManageList.size());
        for (FormulaManage formulaManage : formulaManageList) {
            Object[] params = new Object[]{
                    SysUtils.generateGuid(), DateUtils.getCurrentTimeStamp(), DateUtils.getCurrentTimeStamp(),
                    formulaManage.getSalaryId(), formulaManage.getFormula(), formulaManage.getOpenType(),
                    formulaManage.getCalcOrder(), formulaManage.getIsDeleted(), formulaManage.getAgencyId(),
                    formulaManage.getMofDivCode(), formulaManage.getMeritPayCode()
            };
            paramsList.add(params);
        }

        super.getjdbcTemplate().batchUpdate(sb.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 11; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }

    @Override
    public void deleteFormula(List<String> agencyIds) {
        StringBuilder sb = new StringBuilder();
        sb.append(" update ")
                .append(TableName.FORMULA_MANAGE)
                .append(" set ")
                .append(" update_time = ?, is_deleted = 1 ");
                Object[] params = new Object[]{
                        String.valueOf(DateUtils.getCurrentTimeStamp())
                };
                sb.append(" where ")
                .append(" is_deleted = 0 ");
        sb.append("and ").append(SysUtils.inSql("agency_id",agencyIds));
        this.getjdbcTemplate().update(sb.toString(),params);
    }

}
